﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;


namespace AccesoBD
{


    public class DataAccess : IDisposable
    {

        public String settings = Properties.Settings.Default.CEC;
        public IDbConnection _conexion;
        public Boolean _transaccionEnProgreso;
        public Boolean _transaccionRealizada;
        public SqlTransaction _transaccion;

        public void GetConnection()
        {
            if (_conexion == null)
            {
                dynamic _cadena = settings;
                this._conexion = new SqlConnection(_cadena);
            }
        }

        public void CloseConnection()
        {
            if ((this._conexion != null))
            {
                this._conexion.Close();
                this._conexion.Dispose();
                this._conexion = null;
            }
        }

        public void AddParametros(SqlCommand pCommand, Parametros[] pParametros)
        {
            string _nombre = null;

            foreach (Parametros _element in pParametros)
            {
                _nombre = "@" + _element.Nombre;
                pCommand.Parameters.AddWithValue(_nombre, _element.Dato);
            }
        }

        public void ConfigurarComandoStoredProcedure(SqlCommand pCommand, string pUsp, params Parametros[] pParametros)
        {

            AddParametros(pCommand, pParametros);

            this.GetConnection();
            pCommand.Connection = (SqlConnection)this._conexion;

            pCommand.CommandType = CommandType.StoredProcedure;

            pCommand.CommandText = pUsp;

            if (pCommand.Connection.State != ConnectionState.Open)
            {
                pCommand.Connection.Open();
            }

            if (_transaccionRealizada)
            {
                if (_transaccion == null)
                {
                    _transaccion = (SqlTransaction)this._conexion.BeginTransaction();
                }
                pCommand.Transaction = _transaccion;
            }

        }

        public void Commit()
        {
            if ((_transaccion != null))
            {
                this._transaccion.Commit();
                this._transaccionEnProgreso = false;
                this.CloseConnection();
            }
        }

        public void Rollback()
        {
            if ((_transaccion != null))
            {
                this._transaccion.Rollback();
                this._transaccionEnProgreso = false;
                this.CloseConnection();
            }
        }

        public DataAccess()
        {
        }

        public DataAccess(bool pEsTransaction)
        {
            this._transaccionRealizada = pEsTransaction;
            this._transaccionEnProgreso = pEsTransaction;
        }

        public int executeSQL(string pUsp, Parametros[] pParametros)
        {

            int _return = 0;
            SqlCommand _cmd = null;
            _cmd = new SqlCommand();

            try
            {
                ConfigurarComandoStoredProcedure(_cmd, pUsp, pParametros);


                _return = _cmd.ExecuteNonQuery();

                return _return;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {

                this.CloseConnection();
            }
        }


        public IDataReader executeSQL_Query(string pUsp, Parametros[] pParametros)
        {

            IDataReader _return = null;
            SqlCommand _cmd = null;
            _cmd = new SqlCommand();

            try
            {

                ConfigurarComandoStoredProcedure(_cmd, pUsp, pParametros);

                _return = _cmd.ExecuteReader();

                return _return;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public IDataReader executeSQL_Query(string pUsp)
        {

            IDataReader _return = null;
            SqlCommand _cmd = null;
            _cmd = new SqlCommand();

            try
            {
                ConfigurarComandoStoredProcedure(_cmd, pUsp);
                _return = _cmd.ExecuteReader();

                return _return;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool TestConnection()
        {
            bool result = false;
            try
            {
                GetConnection();
                result = true;
            }
            catch (Exception ex)
            {
                result = false;
                throw new System.Exception(ex.Message);
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        private bool disposedValue = false;

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposedValue)
            {
                if (disposing)
                {

                }

                CloseConnection();
            }
            this.disposedValue = true;
        }


        public void Dispose()
        {

            Dispose(true);
            GC.SuppressFinalize(this);
        }

    }
}